import os
import re
import sqlite3
from time import sleep

from playwright.async_api import async_playwright

from utils.paths import ROOT_PATH

ERROR_REASON = {
    1001: 'Failed to open product page',
    1002: 'Failed to get product brand',
    1003: 'Failed to parse product brand',
    1004: 'Failed to get star rating',
    1005: 'Failed to parse star rating',
    1006: 'Failed to get comment number',
    1007: 'Failed to parse comment number',
    1008: 'Failed to get ship from',
    1009: 'Failed to parse ship from',
    1010: 'Failed to get sold page element',
    1011: 'Failed to get sold page url',
    1012: 'Failed to open sold page',
    1013: 'Failed to get sold information',
}

CLASSIFY_MAP = {
    1001: '时尚配件',
    1002: '男装',
    1003: '厨具',
    1004: '数码',
    1005: '美妆',
    1006: '户外',
    1007: '箱包',
}


class ProductCollector:
    DB_PATH = os.path.join(ROOT_PATH, 'products.db')

    def __init__(self):
        self.conn = None
        self.cursor = None

        self.db_connect()

    def db_connect(self):
        # 连接或创建数据库
        self.conn = sqlite3.connect(self.DB_PATH)

        # 创建游标
        self.cursor = self.conn.cursor()

        # 如果本来不存在数据库文件，则创建表
        self.cursor.execute('''CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code TEXT NOT NULL,
            classify INTEGER NOT NULL,
            name TEXT,
            brand TEXT,
            star TEXT,
            comment_num INTEGER,
            ship_from TEXT,
            is_chinese_seller BOOLEAN,
            is_collection BOOLEAN NOT NULL,
            error_reason INTEGER
        )''')

    def add_product_record(
            self,
            code: str,
            classify: int,
            name: str = None,
            brand: str = None,
            star: float = None,
            comment_num: int = None,
            ship_from: str = None,
            is_chinese_seller: bool = None,
            is_collection: bool = False,
            error_reason: int = None
    ):
        """
        添加产品记录
        """
        params_key = ['code', 'classify', 'is_collection']
        params_value = [code, classify, is_collection]
        if name:
            params_key.append('name')
            params_value.append(name)
        if brand:
            params_key.append('brand')
            params_value.append(brand)
        if star:
            params_key.append('star')
            params_value.append(str(star))
        if comment_num:
            params_key.append('comment_num')
            params_value.append(comment_num)
        if ship_from:
            params_key.append('ship_from')
            params_value.append(ship_from)
        if is_chinese_seller:
            params_key.append('is_chinese_seller')
            params_value.append(is_chinese_seller)
        if error_reason:
            params_key.append('error_reason')
            params_value.append(error_reason)

        params_key_str = ', '.join(params_key)
        params_value_str = ','.join(['?'] * len(params_key))
        params_tuple = tuple(params_value)

        self.cursor.execute(f'INSERT INTO products ({params_key_str}) VALUES ({params_value_str})', params_tuple)
        self.conn.commit()

        # 返回id
        return self.cursor.lastrowid

    def product_exists(self, code):
        """
        判断产品是否已存在
        """
        self.cursor.execute(f'SELECT * FROM products WHERE code = "{code}"')
        result = self.cursor.fetchone()
        if result:
            return True
        else:
            return False

    def get_product_record(self, classify, is_collection=None, page=1, num=100):
        """
        获取产品记录
        """
        if is_collection is None:
            self.cursor.execute(f'SELECT * FROM products WHERE classify = {classify} LIMIT {num} OFFSET {num * (page - 1)}')
        else:
            self.cursor.execute(f'SELECT * FROM products WHERE classify = {classify} AND is_collection = {is_collection} LIMIT {num} OFFSET {num * (page - 1)}')
        result = self.cursor.fetchall()
        return result

    def product_2_collection(self, code):
        """
        标识产品已被采集
        """
        self.cursor.execute(f'UPDATE products SET is_collection = 1 WHERE code = "{code}"')
        self.conn.commit()

    async def add_product(self, url, classify, check_chinese, remote=False):
        # 获取产品code
        search = re.findall(r'(aw/d|dp)(/|%2F)([0-9a-zA-Z]*)', url)
        if not search:
            raise ValueError('Invalid url')
        code = search[0][2]

        if self.product_exists(code):
            raise ValueError(f'[{code}] Product already exists')

        # 获取产品名称
        p = await async_playwright().start()
        try:
            # 打开新浏览器
            if not remote:
                browser = await p.chromium.launch(headless=True)
                browser = await browser.new_context()
            else:
                browser = await p.chromium.connect_over_cdp('http://localhost:9223')
                browser = browser.contexts[0]
            await browser.route("**/*", lambda route: route.abort() if route.request.resource_type == "image" else route.continue_())
        except Exception as _:
            await p.stop()
            raise ValueError('Failed to connect to browser')

        # 打开页面，最多尝试10次
        product_page = None
        for _ in range(10):
            product_page = await browser.new_page()
            try:
                await product_page.goto(f'https://www.amazon.com/dp/{code}', timeout=100000)
            except Exception as _:
                await product_page.close()
                product_page = None
                continue
            break
        if not product_page:
            self.add_product_record(
                code=code,
                classify=classify,
                error_reason=1001
            )
            raise ValueError(ERROR_REASON[1001])

        # 点击下拉框
        dropdown_e = await product_page.query_selector('span.a-dropdown-prompt')
        if dropdown_e:
            for e in await product_page.query_selector_all('span.a-dropdown-prompt'):
                if await e.inner_text() == 'Select':
                    await e.click()
                    sleep(1)
                    await product_page.click('#native_dropdown_selected_size_name_1')
                    sleep(5)

        # 获取产品名
        name = await product_page.inner_text('#productTitle')

        # 获取产品品牌
        brand_str = await product_page.inner_text('#bylineInfo')
        if not brand_str:
            await product_page.close()
            self.add_product_record(
                code=code,
                classify=classify,
                name=name,
                error_reason=1002
            )
            raise ValueError(ERROR_REASON[1002])
        brand = re.findall(r'Visit the (.*?) Store', brand_str)
        if not brand:
            brand = re.findall(r'Brand: (\S+)', brand_str)
            if not brand:
                await product_page.close()
                self.add_product_record(
                    code=code,
                    classify=classify,
                    name=name,
                    error_reason=1003
                )
                raise ValueError(ERROR_REASON[1003])
            else:
                brand = brand[0]
        else:
            brand = brand[0]

        # 获取评分
        star_element = await product_page.query_selector('#acrPopover')
        if not star_element:
            await product_page.close()
            self.add_product_record(
                code=code,
                classify=classify,
                name=name,
                brand=brand,
                error_reason=1004
            )
            raise ValueError(ERROR_REASON[1004])
        try:
            star_str = await star_element.inner_text()
            star = float(star_str.split(' ')[0])
        except Exception as _:
            await product_page.close()
            self.add_product_record(
                code=code,
                classify=classify,
                name=name,
                brand=brand,
                error_reason=1005
            )
            raise ValueError(ERROR_REASON[1005])

        # 获取评论数
        comment_num_str = await product_page.inner_text('#acrCustomerReviewText')
        if not comment_num_str:
            await product_page.close()
            self.add_product_record(
                code=code,
                classify=classify,
                name=name,
                brand=brand,
                star=star,
                error_reason=1006
            )
            raise ValueError(ERROR_REASON[1006])
        try:
            comment_num = int(comment_num_str.split(' ')[0].replace(',', ''))
        except Exception as _:
            await product_page.close()
            self.add_product_record(
                code=code,
                classify=classify,
                name=name,
                brand=brand,
                star=star,
                error_reason=1007
            )
            raise ValueError(ERROR_REASON[1007])

        # 获取发货方
        ship_from_element = await product_page.query_selector(
            '#fulfillment-addressee-name, #fulfillerInfoFeature_feature_div')
        if not ship_from_element:
            await product_page.close()
            self.add_product_record(
                code=code,
                classify=classify,
                name=name,
                brand=brand,
                star=star,
                comment_num=comment_num,
                error_reason=1008
            )
            raise ValueError(ERROR_REASON[1008])
        ship_from_str = await ship_from_element.inner_text()
        ship_from = ship_from_str.split('\n')[-1]
        if not ship_from:
            await product_page.close()
            self.add_product_record(
                code=code,
                classify=classify,
                name=name,
                brand=brand,
                star=star,
                comment_num=comment_num,
                error_reason=1009
            )
            raise ValueError(ERROR_REASON[1009])

        # 获取卖家详情页面链接
        if check_chinese:
            sold_page_e = await product_page.query_selector('#sellerProfileTriggerId')
            if not sold_page_e:
                await product_page.close()
                self.add_product_record(
                    code=code,
                    classify=classify,
                    name=name,
                    brand=brand,
                    star=star,
                    comment_num=comment_num,
                    ship_from=ship_from,
                    error_reason=1010
                )
                raise ValueError(ERROR_REASON[1010])
            sold_page_url = await sold_page_e.get_attribute('href')
            if not sold_page_url:
                await product_page.close()
                self.add_product_record(
                    code=code,
                    classify=classify,
                    name=name,
                    brand=brand,
                    star=star,
                    comment_num=comment_num,
                    ship_from=ship_from,
                    error_reason=1011
                )
                raise ValueError(ERROR_REASON[1011])

            # 打开卖家详情页面
            sold_page = None
            for _ in range(10):
                sold_page = await browser.new_page()
                try:
                    await sold_page.goto(f'https://www.amazon.com/{sold_page_url}', timeout=100000)
                except Exception as _:
                    await sold_page.close()
                    sold_page = None
                    continue
                break
            if not sold_page:
                await product_page.close()
                self.add_product_record(
                    code=code,
                    classify=classify,
                    name=name,
                    brand=brand,
                    star=star,
                    comment_num=comment_num,
                    ship_from=ship_from,
                    error_reason=1012
                )
                raise ValueError(ERROR_REASON[1012])

            # 获取卖家详情文本
            sold_str = await sold_page.inner_text('#page-section-detail-seller-info')
            if not sold_str:
                await sold_page.close()
                await product_page.close()
                self.add_product_record(
                    code=code,
                    classify=classify,
                    name=name,
                    brand=brand,
                    star=star,
                    comment_num=comment_num,
                    ship_from=ship_from,
                    error_reason=1013
                )
                raise ValueError(ERROR_REASON[1013])

            # 判断是否中国卖家
            is_chinese_seller = False
            for char in sold_str:
                if '\u4e00' <= char <= '\u9fff':
                    is_chinese_seller = True
                    break
            if re.search('guangzhou|shenzhen|zhejiang|shanghai|yiwu|wuxi|hangzhou|ningbo', sold_str, re.IGNORECASE):
                is_chinese_seller = True
            await sold_page.close()
        else:
            is_chinese_seller = True

        # 入库
        row_id = self.add_product_record(
            code=code,
            classify=classify,
            name=name,
            brand=brand,
            star=star,
            comment_num=comment_num,
            ship_from=ship_from,
            is_chinese_seller=is_chinese_seller
        )

        print(f'Added product {code}')
        await product_page.close()

        await p.stop()

        return {
            'id': row_id,
            'code': code,
            'classify': CLASSIFY_MAP.get(classify),
            'name': name,
            'brand': brand,
            'star': star,
            'comment_num': comment_num,
            'ship_from': ship_from,
            'is_chinese_seller': is_chinese_seller,
            'is_collection': False,
        }


if __name__ == '__main__':
    u = 'https://www.amazon.com/Large-Banana-Strong-Neutral-Colors/dp/B09Z7L79K6/?_encoding=UTF8&pd_rd_w=JUbXg&content-id=amzn1.sym.a58c4a6b-f441-4c49-bfc9-49006c8fd49e&pf_rd_p=a58c4a6b-f441-4c49-bfc9-49006c8fd49e&pf_rd_r=XKYVGD95WA1197ZTTHJB&pd_rd_wg=k24Fz&pd_rd_r=b54c71e1-d2c8-4679-b208-4135200c8d60&ref_=pd_hp_d_atf_dealz_cs&th=1'
    t = 1001

    pc = ProductCollector()
    pc.add_product(u, t)
